
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_DimAddress_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_DimAddress_Insert]
GO


create procedure dbo.p_DimAddress_Insert (
	@AddressIdentifier varchar (30),
	@StreetNumber int,
	@StreetNumberSuffix varchar (50),
	@StreetName nvarchar (100),
	@StreetType nvarchar (100),
	@streetDirection nvarchar (100),
	@AddressType nvarchar (100),
	@AddressTypeIdentifier nvarchar (100),
	@MinorMunicipality nvarchar (100),
	@MajorMunicipality nvarchar (100),
	@GoverningDistrict nvarchar (100),
	@PostalCode nvarchar (20),
	@Country nvarchar (100),
	@GeoLatitude varchar (20),
	@GeoLongitude varchar (20),
	@GeocodingSpatial geography,
	@AddressKey int output)
as
begin

	-- Disable row counting
	set nocount on

	declare @Error int,
	        @inTranCount int,
	        @Rows int

	select @Error = 0,
	       @inTranCount = @@trancount

	-- Only start our own transaction if caller has not supplied one
	if (@inTranCount = 0) begin transaction txnDimAddressInsert
        else
	    save transaction txnDimAddressInsert

	set @Error = @@error
	if (@Error <> 0) goto cleanup

	insert into dbo.DimAddress (AddressIdentifier,
				    StreetNumber,
				    StreetNumberSuffix,
				    StreetName,
				    StreetType,
				    StreetDirection,
				    AddressType,
				    AddressTypeIdentifier,
				    MinorMunicipality,
				    MajorMunicipality,
				    GoverningDistrict,
				    PostalCode,
				    Country,
				    GeoLatitude,
				    GeoLongitude,
				    GeocodingSpatial)
	  values (@AddressIdentifer,
		  @StreetNumber,
		  @StreetNumberSuffix,
		  @StreetName,
		  @StreetType,
		  @StreetDirection,
		  @AddressType,
		  @AddressTypeIdentifier,
		  @MinorMunicipality,
		  @MajorMunicipality,
		  @GoverningDistrict,
		  @PostalCode,
		  @Country,
		  @GeoLatitude,
		  @GeoLongitude,
		  @GeocodingSpatial)

	select @Error = @@error,
	       @Rows = @@rowcount
	if ((@Error = 0) and
	    (@Rows = 0)) set @Error = -1
	if (@Error <> 0) goto cleanup

	set @AddressKey = scope_identity()

cleanup:

	-- End transaction
	if (@@trancount > 0)
	begin

		-- On error roll back to save point or local transaction else commit
		if (@Error <> 0) rollback transaction txnDimAddressInsert
		else
		if (@inTranCount = 0) commit transaction txnDimAddressInsert

	end

	return @Error

end
GO

grant execute on dbo.p_DimAddress_Insert  to [db_datawriter]
go
